Another instance to develope on.

At the moment, we don't have a development server and develop on our
production server. We develop on "DEV_MyDatabase" and then deploy changes to
"MyDatabase".
This has 2 problems: (1) linked views from other databases have to change,
and (2) sometimes we accidently go into the wrong database.
I have a few ideas:
(1) Setting up a 2nd instance of SQL Server on the production server and
using that instance as the development server.
(2) we have another server that we use for Biztalk, so it has all the
Biztalk databases on that server. I could use that server as the dev server,
but I'm worried about developers going into Biztalk databases. I could creat
e
another SQL Server instance on that server and use that as the dev server.
But i'm not sure how that will affect Biztalk.
If anyone has any advice, please let me know. I have never worked on 2
instances of SQL Server on the same server, so I'm not aware of the pros and
cons.
Thanks,
CraigHi
Totally separate environments are the best solution, now-a-days hardware is
relatively cheap! A second instance is safer than using you alternate
database, but it will require more resources from the production server or
biztalk server depending which one is hosting it. Another option would be to
give the developers their own local copies of developer edition, but
maintenance and syncronisation may prove a high cost.
John
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:BDBD9077-2CF4-4FFC-A89E-4D255D3AB9C9@.microsoft.com...
> At the moment, we don't have a development server and develop on our
> production server. We develop on "DEV_MyDatabase" and then deploy changes
> to
> "MyDatabase".
> This has 2 problems: (1) linked views from other databases have to change,
> and (2) sometimes we accidently go into the wrong database.
> I have a few ideas:
> (1) Setting up a 2nd instance of SQL Server on the production server and
> using that instance as the development server.
> (2) we have another server that we use for Biztalk, so it has all the
> Biztalk databases on that server. I could use that server as the dev
> server,
> but I'm worried about developers going into Biztalk databases. I could
> create
> another SQL Server instance on that server and use that as the dev server.
> But i'm not sure how that will affect Biztalk.
> If anyone has any advice, please let me know. I have never worked on 2
> instances of SQL Server on the same server, so I'm not aware of the pros
> and
> cons.
> Thanks,
> Craig
>|||In my view to do any non-trivial SQL Server development effectively
requires a minimum of three environments: Development, Test and
Production. Check out SQL Server Developer Edition, which is only $50
for development and testing use. For that minimal cost you can run
separate server instances for each non-production environment.
Now which of these will go on physically separate servers or virtual
servers or share the same server is going to be determined by the
nature of your development effort and the size of your budget. I would
suggest that dev should always go on a separate server or at least a
virtual server away from any production application.
Test should mirror production as far as is possible. If your production
is to run on a default instance then you should test on a default
instance. If production is a named instance then test on a named
instance.
David Portas
SQL Server MVP
--

Another Deployment Question

The deployment of reports and datasources via the Deploy function in vs2005
works fine. However we often have to deploy to clients where there is a
firewall with no ports open. We do have Remote Desktop Connection access
and need to deploy reports and datasources by transfering files. (restoring
the entire ReportService database is not granular enough).
We can export and import .rdl files but we cannot find a way to do the same
with Datasources. I know that a datasource .rds file is a brief xml file
that looks like this...
<?xml version="1.0" encoding="utf-8"?>
<RptDataSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name>DataSource1</Name>
<DataSourceID>6aac99b7-1658-479f-be09-fbcb874dbe04</DataSourceID>
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>Data Source=(local);Initial
Catalog=MyApplication</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</RptDataSource>
So it's basically just a connection string with a GUID in it.
How can we deploy Datasources via file transfer? Or is there a better way
in which to deploy reports and datasources to other ReportServer instances
that are behind firewalls.
Thanks,
GaryHello Gary,
As for the ReportServer, currently it can only import report through file
uploading (by provide the report's rdl file). DataSource doesn't support
such importing. And the "rds" file is only used in development environment
(the BI studio or Visual Studio). However, we can use the "rs.exe" utility
to execute script to deploy SSRS report or datasource. The following BOL
reference has provided description and sample on scripting SSRS report and
datasource...
#Scripting Deployment and Administrative Tasks
http://msdn2.microsoft.com/en-us/library/ms159720.aspx
#Script Samples (Reporting Services)
http://msdn2.microsoft.com/en-us/library/ms160854.aspx
The following script block is the one used for creating datasource in the
sample script
=========Public Sub CreateSampleDataSource(name As String, extension As String,
connectionString As String)
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = extension
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False
Try
rs.CreateDataSource(name, parentPath, False, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", name)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
===========
Hope this helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Steven
Now that I understand thet the "rds" is only used in the dev environment it
looks like export/import is the way to go when we are limited to file
transfer.
--
Regards,
Gary Blakely
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:tHvKqMmqGHA.5740@.TK2MSFTNGXA01.phx.gbl...
> Hello Gary,
> As for the ReportServer, currently it can only import report through file
> uploading (by provide the report's rdl file). DataSource doesn't support
> such importing. And the "rds" file is only used in development environment
> (the BI studio or Visual Studio). However, we can use the "rs.exe"
> utility
> to execute script to deploy SSRS report or datasource. The following BOL
> reference has provided description and sample on scripting SSRS report and
> datasource...
>
> #Scripting Deployment and Administrative Tasks
> http://msdn2.microsoft.com/en-us/library/ms159720.aspx
> #Script Samples (Reporting Services)
> http://msdn2.microsoft.com/en-us/library/ms160854.aspx
> The following script block is the one used for creating datasource in the
> sample script
> =========> Public Sub CreateSampleDataSource(name As String, extension As String,
> connectionString As String)
> 'Define the data source definition.
> Dim definition As New DataSourceDefinition()
> definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
> definition.ConnectString = connectionString
> definition.Enabled = True
> definition.EnabledSpecified = True
> definition.Extension = extension
> definition.ImpersonateUser = False
> definition.ImpersonateUserSpecified = True
> 'Use the default prompt string.
> definition.Prompt = Nothing
> definition.WindowsCredentials = False
> Try
> rs.CreateDataSource(name, parentPath, False, definition, Nothing)
> Console.WriteLine("Data source {0} created successfully", name)
> Catch e As Exception
> Console.WriteLine(e.Message)
> End Try
>
> End Sub
> ===========> Hope this helps.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your response Gary,
If you have any further question later, please feel free to post in the
newsgroup.
Have a good day!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Analysis Services: Deploy a cube

I'm making my first attempt at creating a cube using Analysis Services based on my exisiting datamart. Datasource, views, and dimensions have been defined. But comes deploying the cube, it's giving the error saying "A connection cannot be made. Ensure that the server is running." The Deploy Target server and database are the same where my datamart is. Or, maybe I don't know what I'm doing.

Would appreciate any suggestion for my enlightenment. ThanksJodJim,

I am assuming you properly specified target server name. Next steps:
1. Make sure that SQL Server Analysis Services service is running. It is not the same service as SQL service. You can do this in Adminitrator Tools ->Services. This is most likely cause why you cannot deploy
2. Make sure you are member of administrators local group on SSAS server. This will give you admin right required to deploy database.

If that does not work:
Start SQL Server Management Studio and connect to analysis services. Any problems? Any messages?

Vidas Matelis
http://www.ssas-info.com
http://www.ssas-info.com/VidasMatelisBlog

Analysis Services Step by Step - connection problem

On page 56 of SQL Server 2005 Analysis Services Step by Step, I tried to DEPLOY the project, and it failed with this msg:

Error 1 The project could not be deployed to the 'localhost' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

I downloaded the trial version from MS and installed in om my laptop from where im running my tutorial - what could be wrong? (Im a newbie to SQL so I have no clue)

You need to make sure Analysis Server is running on your machine. Go into Service control manger and make sure SQL Server Analysis Services service is running.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward Melomed wrote:

You need to make sure Analysis Server is running on your machine. Go into Service control manger and make sure SQL Server Analysis Services service is running.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

I have the following services running:

SQL Server, SQL Server Agent, SQL Server Browser, SQL Server FullText Search, SQL Server Integration Services and SQL Server VSS Writer - I can't see any Analysis related services on my services list?

|||

It is working now - thanks alot for your help.

I reinstalled my solution and for some reason I didnt have the Analasis services correctly installed.