Deploying SQL Express Throughout The Organization/Enterprise
May 17, 2007
What are some recommended methods for deploying SQL Express throughout an entire organization? It appears that Active Directory/GPO deployment is out of the equation.
Okay, I've read massive topics on ClickOnce, and embedded database applications, etc, etc.
I need a handful of good suggestions how I can create a deployment package, to get my sql express database onto a SQL Express server.
The database will need to be multi-user, because I am also developing a WinForms application to connect to the SQL Express database.
I'm thinking I need to use some automated scripting features, to generate scripts for the database once I am done (unless you have a better suggestion).
After that, I need to know what to do with those scripts, and how can I create a setup / deployment packages to run those scripts against a SQL Express server.
The other alterntative obviously is to copy my sql express database, and run an attach command. This will work the first time, only because this will have been the first deployment of this database.
Please keep in mind when answering this question, that I will not be embedding the database, and as far as I understand ClickOnce is a feature for use with embedded databases or user instance databases (not sure).
Hello, I work for a company that manages computers and their software using Active Directory. One of our packages requires SQL Express 2005, and so far I've been unable to find a good resource online. All I need to do is find a .msi file or a batch script that will install it. Thanks!
I have a small ASP.Net application developed in VS 2005 that I would like to deploy with SQL Express.
I have included the MDF file in the Application and added SQL Express as a pre-requisite for the Setup and Deployment project.
It installs SQL Express but when it tries to connect I receive: "This is not a valid login Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITYSYSTEM'."
I don't seem to have any options using the "prerequisite options in VS 2005 in terms of command line deployment parameters.
Likewise I would prefer to attach the database to the instance (because I need to write a connection string from a PDA using RDA and I have zero clue how to do RDA directly back to the file).
I have developed a windows mobile 5.0 application that is currently syncing with sql server 2005. I want to deploy the application with sql server express for the client. What do I need to do to make this work?
Hello 1.) Is there any solution for integrating the deployment of the sql server express into the setup project of my vb 2005 app ? 'Cause it is too much to install the framework... then the sql server and then the app....... Any help would be appreciated!
Hi there. I've created a windows forms app that connects to a sql server express database. now that it's working on my box, i need to install the entire solution on the end user's machine. what's the best way to move the database? I found this article: http://msdn.microsoft.com/en-us/library/ms165716.aspx but i'm wondering if anyone can give me some insights based on your own experience. I'm using ms visual studio 2005 and sql server express 2005. I'm hoping that all i'll really have to do is copy everything over to the new machine and then edit my connection string in the .exe.config file for my app.
Can any one clarify me the doubt in deploying the application build with SQL Server Express 2005 as database and Vb.Net 2005 as frontend with the same setup made for delopying application. will the server installation and database creation can be made automatic.
If this is possible then what are the steps in doing so and the files to be included while enbeding with vb.net setup.
I've written a ClickOnce app that deploys a Sql Server Express data file (.mdf) and log file (.ldf) along with the other application files. SQL Express SP2 is a prerequisite for this app. After the applicaiton installs and starts up it copies these files to a specific folder and attaches them by calling the AttachDatabase() method of a Microsoft.SqlServer.Management.Smo.Server object.
This method works fine using Windows XP, however when I run it on Windows Vista I get the following exception:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Attach database failed for Server 'localhostSQLEXPRESS'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Unable to open the physical file "C:WashMasterDatabasewasshmaster.mdf". Operating system error 5: "5(Access is denied.)".
This exception occurs when I try to programmatically attach the database.
I've searched through the MSDN forums for a solution and I've only found two suggestions:
1.) Create all database connections using a User Instance. 2.) Create the database (using SQL) in the target folder, rather than copying it over from the deployment folder.
I don't like the idea of using User Instances because my entire data access layer is currently written using Ole and I would rather not rewrite it if I don't have to. I cannot find any way to use User Instances with Ole.
I also don't like the idea of creating the database using SQL because it is fairly large.
Here are my questions:
1.) Can User Instances be used with Ole?
2.) Can I somehow programmatically set the permissions of the copied data and log files so that SQL Express can access them without error?
3.) Is there a better method for doing what I'm trying to do or am I destined to have to either rewrite in data access layer so I can use User Instances or create my large database rather than copy it to the target directory?
I have created an installer for deploying Sql Server Express. My installer will start the process of installing Sql Server Express. However the installation of SqlServer Express will fail because it says another installer is currently running (which is my installer). Is there anyway to work around this problem?
The reason why I would like to create an installer for the installation of Sql Server Express is because I would like to configure the SqlServerExpress without the user fiddling with the ini file.
I have a web app that uses two SQL Express databases. One of the databases is the membership database. I am deploying the app to a hosted environment. Here is my connection string:
Could not open new database 'Akamojo_ASPNETDB'. CREATE DATABASE is aborted. Cannot attach the file 'e:hostingmemberakamojoTournamentLogApp_DataAkamojo_ASPNETDB.mdf' as database 'Akamojo_ASPNETDB'. File activation failure. The physical file name "c:inetpubwwwrootTournamentLogApp_DataAkamojo_ASPNETDB_log.LDF" may be incorrect. The log cannot be rebuilt when the primary file is read-only.
The mdf file is not read only. In the hosted environment the database is on the E drive. In my dev environment the database is on the C drive. From the error message, it appears that when it tries to build the log file it is trying to build it on the C drive in a path that doesn't exist in the hosted environment.
Hello, I am having a difficult time with a complicated task. I have developed numerous SSRS reports for my application and want to deploy these reports with my application. The application uses SQL Express Edition with Advanced Services. I have written an installer to automate this process, but my problem is getting the SSRS reports that I developed on my server incorporated into the ReportServer database created during the install of SQL Express Edition with Advanced Services. I have read numerous good posts and articles regarding the rsconfig.exe and rskeymgmt.exe utilities, but I am having problems. The main problem seems to be dealing with the encryption key. I have done a restore of the ReportServer database using a backup of the ReportServer database I used to develop my SSRS reports. This worked well with the exception that I can't seem to get past an error message stating that reportiong services has not been initialized. Keep in mind, I need an automated way to do this. Anyway, I have tried using rskeymgmt to restore a backed up copy of the encryption file that I took from the server where I developed the reports. I get an error message that scale-out deployment is not supported in this version or something like that. I can't delete the encrypted data because then the connection information for my shared data source is deleted also. Again, please keep in mind I need all of this to occur behind the scenes during an install without end-user intervention.
Anyways, sorry for such a long post. I would appreciate any help anyone may be able to provide.
Hello, I would like to deploy a per-machine Express database as part of my VS2005 C# desktop application. The deployment must work with both XP and Vista from the same setup files. I'm not using One-Click installation. I don't want my users to have to configure Express with the Surface Area Configuration tool.
Since the database cannot go in C:Program Files because of Vista ACL issues, I am using COMMONAPPDATACompanyNameAppName as the install path for the database and other user writable application files. In XP this expands to C:Documents and SettingsAll UsersApplication DataCompanyNameAppName and in Vista it is C:ProgramDataCompanyNameAppName. Neither of these paths have write privileges for unprivileged users, so the install program needs to change the write permissions at install time. Right now I am using SetACL ( http://setacl.sourceforge.net/ ), to do that and the install goes fine with write permissions on the companyName folder and it's children for all users.
But I can't connect to the mdf file in my COMMONAPPDATACompanyNameAppName folder. If I am the administrator/installer it works fine, but as an unprivileged user I get different errors, but mostly "Could not login user ...". For debugging, I have also tried installing the database in the "AppFolder" directory (which for my app in both Vista and XP is C:Program FilesCompanyNameAppName) and using "User Instance=true" in the connection string, and the connection is made but (at least on XP) the unprivileged user cannot write to the database because the folder lacks write privileges for that user.
I suppose one thing I could do would be to change the folder/file permissions in the AppFolder, but I am reluctant to do that because I have read that Microsoft does not guarantee that the "Compatibility" feature in Vista for the Program Files directory will be available in future releases/service packs.
So is there a way to do a simple installation/db connection for a per-machine database located in a COMMONAPPDATA path?
I understand the log files (LDF ) and data files (mdf ) should be on a different drives . I believe it leads to greater availabilty and speed . Are there any other reasons for to keep this on a separate drive.
Also what considerations I should take care while creating a database of around 100 GB . (use of filegroups , growth % etc ). Is there any connection of number of users to number of disks SQL data file to be spread to . Also do I need to take care (through hardware / software for a Quad core CPU ) to take full advanage of Quad core CPU.
Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks
I have several sub reports and some graphics I don't necessarily want the users to see or open. Is there a way to hide items from view but still have them available for execution? I tried creating a sub folder and moving the sub reports but the calling reports couldn't find them.
I need to write a sql that generate the hierarchy in an organization.
Below an example
emplid empname supervisor_id superv_name 1 subu null null 2 vid 1 sub 3 ram 4 satis 4 satis 2 vid
I need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.
Whoever joining new to org the hierarchy
empid empname supervisor_id superv_name 3 ram 4 satis 4 satis 2 vid 2 vid 1 subu 4 satis 1 subu 3 ram 1 subu 3 ram 2 vid 5 kumar 1 subu 5 kumar 4 satis 5 kumar 2 vid 1 subu null null
In a large organization who would typically be tasked with support ing SSIS - the database team, an application team, etc... SSIS is new to our Organization, we are attempting to put it into the correct organization. We current have SSRS that is supported by an application team, the SS DB is supported by the DB team.
I am struggling with adding budget numbers to a cube. The main reason being that the budget is *not* on the finest granularity (employee) with regard to the organization hierarchy but on a coarser one (team).
The organization hierarchy is a "flat" (not parent-child) hierarchy that looks about like that:
employee -> team -> teamgroup -> region -> country
As mentioned I now have budget numbers that are defined on the team-level (not on the employee level as "regular" measures). I would know assume that I could put the budget data into its own table and "link" it with the organization through the "team" attribute. I would do that on the "dimesion usage"-tab.
The problem with this approach is that the organization is changing (SCD type 2). This essentially means that by linking to the "team" attribute the aggregation of the budget data on higher levels of the organization hierarchy can be ambiguous (at least that is what I understand).
That would mean that on some point in time team 2 (consisting of one employee, Jeanne) moved from teamgroup 2 to teamgroup 3. Just for the sake of a simple example.
Now, what am I to do with my budget data in this situation? I cannot link it to the teamId, because teamId = 2 for example cannot specify if the value should be aggregated into teamgroup 2 or teamgroup3...
I have a feeling that this got something to do with the design of the organization-table but I am unsure about what the actual problem is. Any hint, pointer or solution would be appreciated. If the question is unclear, please let me know and I will try to clarify.
I want to remove the month and have one total per organization number, as well as remove NULL as show as 0.
Code: select * from yearproduction pivot( sum(Gas_Prod) for Year in ([2008], [2009], [2010], [2011], [2012])) as YearlyProduction order by Organization_Number, Month
I need to write a sql that generate the hierarchy in an organization.Below an example
emplid empname supervisor_id superv_name 1 subu null null 2 vid 1 sub 3 ram 4 satis 4 satis 2 vid
i need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.whoever joinng new to org the hierachy should be follwed
empid empname supervisor_id superv_name 3 ram 4 satis 4 satis 2 vid 2 vid 1 subu 4 satis 1 subu 3 ram 1 subu 3 ram 2 vid 5 kumar 1 subu 5 kumar 4 satis 5 kumar 2 vid 1 subu null null
The data limit in SQL Express is 4 GB and SQL Enterprise is unlimited. I have SQL Express installed on my desktop machine. and SQL Enterprise on my web server. Now, I am thinking to develop solution in ASP.NET with SQL Express. and finally upload the solution on web server, where SQL Enterprise is installed. So, will there be any problem in such case ?In other words, the mdf file created by SQL Express and SQL Enterprise is same or different ?
Hi My company have purchased Windows 2003 web edition, where as I read we cannot run Sql Server 2005. I just want to know is there any service pack or some thing by which we can run sql server 2005 on Windows 2003 web edition??? furthermore, We can run sql server express edition on that OS , infact we are running but the issue is we are unable to use SQL Express Edition on network enviroment is there any way to do that ? If we develop application in sql server enterprise and deploy it on sql express ??? is this possible What are the disadvantages of using SQL Express over sql server enterprise A detail answer would be appriciated. cheers.....
I have one machine using Enterprise(personal edition) and another using Express Edition. I tried everything but I could not manage connecting from Enterprise to Express Edition. I have the question:
Can I connect from Enterprise to Express If I can how can I?
I know may be it is a strange question, but also may be i dont understand well so i hope that some one make it clear for me.
I know that the database size limit in sql express is 4GB .. and now i have known that late after i had built a big application with sql express .. with many tables, triggers, and stored procedures.
So now i wish to make my database size is unlimited, so after buying sql enterprise .. how can i make my sql express file just a sql enterprise file ?
I have sql 2005 express on my Dev machine but am planning on using a full sql 2005 database where i deploy my program and i have 2 questins please
1. Will my sql express database work with the full version of SQL 2005 or do i import it or something ( DB will get bigger than sql express max at some point)and
2. I was intending using stored procedures, can these also be easily be used/ transfered on am SQL 2005 database
I have sql server express edition on my local machine and sql server enterprise edition on the server. When I try to use the import export tool on the server . it gives me this error TITLE: SQL Server Import and Export Wizard------------------------------Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Destination - DOR_outFile_txt" (193).------------------------------BUTTONS:OK------------------------------ Is this error due to the fact that I have sql server express on my local machine? If yes, then does that mean that if I am programming from my local machine, the version of SQL on the server doesn't matter?
Ive received a .bak file of our company db from our hosting company and would like to import it into the SQL 2005 Express machine however, Ive never done this before using 2005. Previously with SQL 2000, I used the enterprise manager to view/import data into the database but Im unable to find this feature with 2005.